CREATE  FUNCTION FN_GET_STRINGS_IN_STRING
( 
	@VENDOR_STR VARCHAR(2000) 
)
RETURNS @FN_RESULT TABLE ( VENDOR VARCHAR(50) )
AS
BEGIN

DECLARE @VENDOR VARCHAR (50)
DECLARE @STR_INDEX INT
DECLARE @TEMP_STR VARCHAR(50)
DECLARE @NEXT_CHAR CHAR(1)
DECLARE @PS_COUNT INT

--INSERTING THE VENDORS PASSED AS PARAMETER INTO A TEMP TABLE
SET @STR_INDEX = 1
SET @TEMP_STR = ''

WHILE( @STR_INDEX <= LEN( @VENDOR_STR ) )
BEGIN
	SET @NEXT_CHAR = SUBSTRING( @VENDOR_STR, @STR_INDEX, 1 )

	IF( @NEXT_CHAR = ',' )
	BEGIN
		IF @TEMP_STR IS NOT NULL AND @TEMP_STR != '' AND @TEMP_STR != ' '
		BEGIN
			SET @TEMP_STR = RTRIM( LTRIM( @TEMP_STR ) )
			SELECT @PS_COUNT = COUNT(*) FROM @FN_RESULT WHERE VENDOR = @TEMP_STR
			IF( @PS_COUNT = 0 )
			BEGIN
				INSERT INTO @FN_RESULT( VENDOR )  VALUES( @TEMP_STR )
			END
		END
		SET @TEMP_STR = ''
	END
	ELSE IF( @NEXT_CHAR != '' or @NEXT_CHAR = 0 ) -- CHECKING 0 FOR SPACE ' '
	BEGIN
		SET @TEMP_STR = @TEMP_STR + @NEXT_CHAR
	END
	SET @STR_INDEX = @STR_INDEX + 1
END

--INSERTING THE LAST VENDOR IN THE STRING
IF( @TEMP_STR != '' AND @TEMP_STR IS NOT NULL )
BEGIN
	SET @TEMP_STR = RTRIM( LTRIM( @TEMP_STR ) )
	INSERT INTO @FN_RESULT( VENDOR )  VALUES( CONVERT(VARCHAR, @TEMP_STR ) )
	SET @TEMP_STR = ''
END

RETURN
END
